Case (3) Simple financial data analysis
Project 2: Calculating the excess return of a stock
Design a program to calculate the quarterly and annual returns of a stock and to calculate the excess return (i.e. relative return) of a single stock's return relative to the average stock market return over the same period.
This project uses tushare's Python SDK to obtain the data The details of the methodology are the subject of a new article.
# -*- coding: utf-8 -*-
"""
Created on Sat Sept 19 9:30:36 2020
@author: mly
"""
import tushare as ts
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib import ticker
plt.rcParams['font.sans-serif'] = ['SimHei'] # Used to display Chinese labels properly
startday = '2015-01-01'
endday = '2020-04-01'
tscode = '600519'
tsindx = 'sh'
df1 = ts.get_k_data(tscode, start = startday, end = endday, ktype = 'M')
df2 = ts.get_k_data(tsindx, start = startday, end = endday, ktype = 'M')
df1.to_excel('600519.xlsx',index=False)
df2.to_excel('sh.xlsx',index=False)
df1=pd.read_excel('600519.xlsx',dtype={'code':'str'})
df2=pd.read_excel('sh.xlsx',dtype={'code':'str'})
df = df2[['date', 'close']].copy()
df.rename(columns={'close': 'indclose'}, inplace = True)
df = pd.merge(df[['date', 'indclose']],
df1[['date', 'close']], on = 'date', how = 'left')
df.fillna(method = 'ffill',inplace=True) #successively to the filling
df.fillna(method = 'bfill',inplace=True) # Further forward filling
# Calculating monthly and cumulative rates of return
df['stk_log_ret'] = np.round(np.log(df['close']/df['close'].shift(1)), 4)
df['ind_log_ret'] = np.round(np.log(df['indclose']/df['indclose'].shift(1)), 4)
df['stk_log_ret'].fillna(method = 'bfill',inplace=True) # Further forward filling
df['ind_log_ret'].fillna(method = 'bfill',inplace=True) # Further forward filling
df['xd_ret']=df['stk_log_ret']-df['ind_log_ret']
df['xd_ret'].fillna(method = 'bfill',inplace=True) # Further forward filling
df_list=list(df['stk_log_ret'].values)
print(df['stk_log_ret'].values)
ret_year=[]
ret_quarter=[]
for i in range(len(df_list)//3):
ret_quarter.append(np.round(df_list[3*i]+df_list[3*i+1]+df_list[3*i+2],4))
ret_quarter1=pd.Series(ret_quarter)
for n in range(len(ret_quarter)//4):
ret_year.append(np.round(ret_quarter[4*n]+ret_quarter[4*n+1]+ret_quarter[4*n+2]+ret_quarter[4*n+3],4))
ret_year1=pd.Series(ret_year)
quarter_list=[]
year=[]
df_index=list(df.date)
for value in df_index:
tempvalue = value.split("-")
if tempvalue[1] in ['01','02','03']:
quarter_list.append(tempvalue[0] + "Q1")
year.append(tempvalue[0])
elif tempvalue[1] in ['04','05','06']:
quarter_list.append(tempvalue[0] + "Q2")
year.append(tempvalue[0])
elif tempvalue[1] in ['07', '08', '09']:
quarter_list.append(tempvalue[0] + "Q3")
year.append(tempvalue[0])
elif tempvalue[1] in ['10', '11', '12']:
quarter_list.append(tempvalue[0] + "Q4")
year.append(tempvalue[0])
quarter_set = set(quarter_list)
quarter_list = list(quarter_set)
quarter_list.sort()
year_set = set(year)
year = list(year_set)
year.sort()
year.pop() #Delete as 2020 is not yet complete
ymajorFormatter = ticker.FormatStrFormatter('%.2f%%') # Formatting of axis label text
fig = plt.figure(figsize=(14, 24))
ax1 = fig.add_subplot(3, 1, 1)
fig.subplots_adjust(bottom = 0.2)
plt.ylabel('季度收益率')
plt.xticks(rotation = 60)
ax1.yaxis.set_major_formatter(ymajorFormatter) # Show percentage
ax1.plot(quarter_list, ret_quarter1*100, '-cs', lw = 1.5, label = tscode+' 季度收益率')
ax1.legend(loc = 'upper left')
ax2 = fig.add_subplot(3, 1, 2)
plt.ylabel('年收益率')
plt.xticks(rotation = 60)
ax2.yaxis.set_major_formatter(ymajorFormatter) # 显示百分比
ax2.plot(year, ret_year1*100, '-gp', lw = 1.5, label = tscode+' 年收益率')
ax2.legend(loc = 'upper left')
ax3 = fig.add_subplot(3, 1, 3)
plt.ylabel('相对收益率')
plt.xticks(rotation = 60)
ax3.yaxis.set_major_formatter(ymajorFormatter) # 显示百分比
ax3.plot(df.date, df['xd_ret']*100, '-rp', lw = 1.5, label = tscode+' 相对市场收益率')
ax3.legend(loc = 'upper left')
plt.show()
Results of the run.
